Release notes

Summary: In the Forecast Review Batch worksheet, the used range for this batch is set at XFD, which could cause performance issues.

Resolution: Adjusted the used range by deleting unused formatting and the Last Saved columns.

Summary: In the Source tab of the Import RF Actuals utility, the SQL Statement has 2014 hard-coded as the reference year.

Resolution: Corrected by updating the InitiativeID and by adding the variable for the year in the General tab. Now the update to the import will prompt the user for the year to include.

Summary: In the RF Deductions calculator, when the calc method for an item is Last Saved, the values populated do not roll to the new quarter when crossing quarters. The Save2BD line is hard-coded and does not allow for values to roll based on RF period selected.

Resolution: Corrected by updating the RF Deductions calculator control sheet to allow the values to roll forward.

Summary: If a user’s system does not have the Rolling Forecast Provider license, the Axiom Rolling Forecast Security utility produces an error when a user tries to open it.

Resolution: Corrected by changing the Security Update utility so that, instead of producing an error, the Provider section is hidden if a user does not have the Provider license.

Summary: In the Forecast tab of the Rolling Forecast plan file, adjustments are not copying correctly when months roll forward because of an error in one of the cell formulas. As a result, users see incorrect Base and Variable Expense amounts in column S of the tab.

Resolution: Corrected by revising how the standard adjustments from the Waterfall table are retrieved in columns LA:LC.

Summary: For both Volumes and Other Volumes calculation methods, the sum of the three months in columns X, Y, and Z do not add up in Row 1 forecast quarter (column AB) of the calculation method. Instead, the current formula is adding Row 9 of the calculation method, which is including the new one-time adjustments from columns X, Y, and Z.

Resolution: Corrected by changing the reference in column AB for the calculation methods: IP Volume, OP Volume, Other Volume. This will now sum volume based on the first volume row of the calc method.

Summary: There are two issues with the RF Target Variance report:

1. In the legend of the RF Target Variance report, Red, Yellow, and Green triangles indicate how the system is trending. However, as it is currently formulated, users will not see a yellow triangle because that would indicate that they have one quarter exactly equal to the previous quarter, which is not likely.

2. The report needs a refresh variable that allows you to pull the period you want to compare to from the RF_Waterfall table instead of pulling from the last month in the table. For clients that forecast only once per quarter at the end of a quarter, the RF_Waterfall table for the prior month will be blank unless they roll each month, regardless of whether they forecast for each month.

Resolution: 1. Adjusted the performance tracking variable to remove the Yellow (neutral) triangle indicator. 2. Corrected by adding the prior period selection so that the label on the report reflects the project/forecast period.

Summary: On the Drivers tab of RF Drivers, actuals are not being pulled in for the last quarter of actuals.

Resolution: Corrected by fixing the formula used in the column that pulls in actuals.

Summary: The RF (plan file) Template has the following issues:

1. In the OP Revenue NetPerUnit calculation method, the Average Revenue Rate per unit calculation in column AB (and AC) is not calculating correctly. In the last row of the calc method, the formula is referencing IP Statistics instead of OP Statistics.
   Resolution: In the IP Revenue_NetPerUnit calculation method, corrected the formulas where applicable. In the OP Revenue_NetPerUnit calculation method, copied the corrected formulas to the relevant rows. In the Oth Pt Revenue_NetPerUnit calculation method, corrected the formulas as needed.
2. For the IP Census calculation method, the Admissions in row 9 of the calculation method are not interfacing with the historical data in column T, which comes from the sum of columns Q:S. Also, CMIAdjAdmissions is not interfacing with the historical periods. Data fields that were previously interfaced directly into the report section were moved, but the report columns do not reference the added interfaced columns.
   Resolution: Corrected by copying the correct formulas to the right cells.
3. The Benefits as a Percent of Salaries calculation method, row 9 (Percent of Salaries) is zero because row 11 of the calculation method is referencing Salaries in column AX for which there is no data displaying. Additionally, row 12 of the calculation method for the monthly columns is divided by a factor of 1,000 (cell W31).
   Resolution: Corrected by copying the formulas to the right cells.

4. In the Return Rate calculation method, column T of row 9 for the Adjusted Cash Balance has different totals for columns T and U. Historical Cash is incorrectly referencing the RFHistory! tab.
   Resolution: Corrected the formula in column F of the calculation method to correctly reference the RFHistory! tab.

5. The Other Deductions calculation method is not interfacing into the Forecast tab of the plan file, so the Bad Debt is not being used or forecasted in the plan files. The applicable queries are not including OtherDeductions as a selection.
   Resolution: Corrected the queries to not exclude the Bad Debt.
6. For the Pct of Salaries and Rate per FTE calculation methods, column AC for both are referencing the wrong row for the "Last Quarter" test condition.
   Resolution: Corrected by changing the formulas so that the calculation methods are referencing the correct rows.

Summary: A number of issues with the Benefits as a % of Salaries calculation method is causing calculation problems in plan files that use it.

Resolution: Corrected by adjusting the affected formulas to fix the scaling factor.

Summary: In the Forecast tab of the RF plan file, when the Current Month columns (Q, R, S) are hidden but column U displays, in row 11 of the IP Volume, OP Volume, and Other Volume calculation methods, the formula is dividing by column T instead of column P.

Resolution: In the RF plan file template, the Current Quarter is either column T or column U, depending on the user’s choice. Column T is the result of the three underlying months, while column U is the standalone, quarter-only column. The two columns work independently of each other, but in both cases, column P is the previous quarter. The formula is calculating results from the wrong column. Corrected by updating the formula in the Calculation Method Library.

Summary: In the Forecast tab of the RF plan file, the current quarter is in column T if users hide the month columns. If users do not hide the month columns, the current quarter is in column U. Currently, the formula only reads column T, regardless of whether the user hides the month columns or not.

Resolution: Corrected by adding an “if” statement to the formula so that it reads column U when months are not hidden.

Summary: In the Forecast tab of the RF plan file, the Benefits as a Percent of Salaries calculation method is zero because row 11 of the calculation method is looking for Salaries in column AX but there is no data displayed there. Also, row 12 of the calculation method for the monthly columns is divided by cell W31, which is a scaling factor of 1,000.

Resolution: Corrected by adjusting the scaling factor in the Salaries calculation method.

Summary: In the Forecast tab of the RF plan file, the Annual Average and Last Quarter forecast methods are ignoring any historical negative growth rates in the historical periods.

Resolution: Corrected the formula logic for the IP Census calculation method.

Summary: In the RF Drivers, there is a typo in the pre-populated label “ Summary - Label for Adjusted EBIDA.” Instead, it reads “EBSIDA.”

Resolution: Corrected by fixing the formula that pre-populates the affected label.

Summary: In the Forecast tab of the RF plan file, when an expense is split between Variable and Fixed (the Fixed is set to use Last Quarter as the forecast method), the Fixed portion continues to decrease quarter after quarter.

Resolution: Corrected by adding missing parentheses at the end of the formula that applies to column AD, row 8 and row 15 of the calculation method.

Summary: In the Initiatives tab of the RF plan file, when adding new salaries, the dollars code that is added with the salaries is keyed to H_JC_Hours. The salary codes and hours codes are matched up in the drivers. The hours codes should be set to pull in based on the driver setup.

Resolution: Corrected by adjusting the formula to bring in the correct mapped hours.

Summary: Extra commas in the cell formula for the RF Expense calculation method is causing two additional values of zero to be added to the calculation.

Resolution: Corrected by removing the commas from the formula.

Summary: Formula errors in the Axiom Rolling Forecast Security Update Utility spreadsheet are causing existing non-Rolling Forecast users to have their security set to No Access when administrators change security for existing Rolling Forecast users. The utility updates security for Rolling Forecast users in tables that are shared with Axiom Budgeting and Performance Reporting. The utility queries all users into the utility so that the Rolling Forecast administrator can assign access to the Rolling Forecast module for selected users. However, other existing users that are not granted access are also present in the utility, and the [Save] tag may become activated for them as well. When this happens, the system places a blank in the existing filter for Financial, Payroll, and Provider table types for the non-Rolling Forecast users, which overwrites the filter settings for those users.

Resolution: Corrected by changing the formulas and adding logic where needed so that the original table filter settings for non Rolling Forecast users are preserved and not overwritten.

Summary: In the Forecast tab of the RF plan file, errors in the IP Census calculation method formula are causing Growth Rates to become exponential when a user enters a one-time adjustment in the Next Quarter month columns (columns Y or Z).

Resolution: Corrected by rewriting affected formulas to remove the errors.

Summary: RF Deductions Calculator, Payor Codes tab: the order of the payor classes is not the same on the Ddeductions tab as it is on the PayorCodes Tab. This is caused by a sorting issue.

Resolution: Corrected by parsing the number to the correct column on the Deductions tab (used a sheet sort on that column for the Axiom Queries that bring in the payors).

Summary: In the Forecast tab of the RF plan file, when using the Expense calculation method, if the Forecast Method is set to 100% fixed, a variable expense is calculated instead because Column U is not referencing the allocation decision (variable/fixed %), causing non-zero variable values.

Resolution: Corrected the formula in Column U.

Summary: A defect in the RF Adjustments - Expense Driver is causing the FS_Detail column to display Physician and Mid-Level salaries inaccurately, and salary rates are not coming into the plan files. It appears that the driver’s Adjustments_Expense tab has the wrong codes in certain columns, displaying R_Salaries_Phy and R_Salaries_Mid instead of R_SalariesPhy and R_SalariesMid. The underscore after the word Salaries needs to be removed.

Resolution: Corrected by updating the Adjustments tab to reference the current RFCode/FSDetail/FSPayor options.

Summary: In RF Drivers, the refresh action on the Global Data tab is not refreshing data after a user edits, deletes or adds a record.

Resolution: Corrected by separating the refresh updated data button from the formatted grid, thereby enabling the conflicting refresh triggers to work together.

Summary: When saving a forecast to the Waterfall table, a user selects the Waterfall table version to save to. However, the RF_Forecast table in Mapping Tables is hard-coded to RF_Forecast; it should be a variable so that it can point to the scenario version of the RF_Forecast table.

Resolution: Corrected by creating the conditions to make the save to Waterfall utility fully scenario compliant.

Summary: In RF Drivers, when RFPlanGroup Exceptions for the revenue are set up and saved, they do not appear when the user goes back into the form to review. The data is still in the table, but it is not flowing to the form. To get the data to appear, the user must click the Save button.

Resolution: Corrected by changing the manual refresh to ON for the AdditionalQueries tab.

Summary: The RF Balance Sheet and Cash Flow calculator utility has a number of issues:

1. The utility is missing a row for A_BondAmort, which is a standard selection in FSDetail.

   Resolution: Updated the calculator’s Control_Sheet to add A_BondAmort to the Other Assets section for row 92.

2. A section needs to be added to the bottom of the calculator worksheet so that everything works as expected.

   Resolution: Updated the calculator’s Control_Sheet to add an A_BondAmort section to rows 269 - 276.

3. The formulas in the top of the worksheet error out because there is an error in the formula for cell N41 that makes the date format incorrect.

   Resolution: Referenced the correct period(s) in H41 - I41 from Variables!AB18:AB19.

Summary: In the RF Deductions Calculator, the Summary tab needs to have the same sort order update as the Deductions tab.

Resolution: Corrected by adding a sheet sort on Column D in the calculator’s Control Sheet.

Summary: In the RF Deductions Calculator, there is a Compute/Forecast drop-down in the Inpatient/Outpatient Revenue by Payor sections. The Forecast option is supposed to use the forecasted payor mix to allocate. If Compute is selected, it uses the run rate within the utility. However, the following issues are occurring:

1. When Forecast is selected, if the payor mix is not completed for all areas, in the utility, the Forecast calculations are not tying out to the totals unless the user updates the volumes.

2. When Compute is selected, instead of calculating based on the historical trend from the last quarter, it is referring to the columns to the right, which are for the RF_Monthly data.

Resolution: Corrected by updating the formulas in the calculation method sheet to correctly pull the data for the Compute option. This pulls future periods as long as the data is in the RF_Forecast table.

Summary: In the RF Deductions Calculator, the Discharges by Payor section defaults to 100% Medicare, no matter which forecast method is selected. Patient Days and Outpatient Visits are defaulting to what was last saved with the forecast method.

Resolution: The drop-down in the AL column is meaningless for the Medicare section. Allowing the choice to have an impact can allow for results that do not total to 100%. Corrected by removing the drop-down option from Column AL.

Summary: If the RF_Monthly table is cleared or reset, the statistics preserved in the RF_StatFilter table are not being retrieved, even though the RF_StatFilter table was created expressly for this purpose. The RF_Input Monthly Stats form is pulling the filter from the RF_Monthly table. It should be pulling the filter from the RF_StatFilter table and saving the filter back to that table and the data to the RF_Monthly table.

Resolution: Corrected by changing the primary table to RF_StatFilter and adjusting the fields to return from RF_StatFilter with data from the RF_Monthly table.

Summary: The following issues occur when a user creates and saves an approved initiative, then creates and processes a plan file with the initiative, and then views the RFInitiatives worksheet:

• Statistics are not displayed.
• For Revenue, only the total displays, not the line that shows the statistic to which it is tied or the per unit amount.
• Changes to benefit percentages are not displayed.
• Patient revenue is not calculating in the Total Patient Revenue section and therefore, not displayed in the Est Net Results field.

Resolution: Corrected by updating the formulas in the affected cells.

Summary: The Rolling Forecast Security Update utility is updating existing RF Analysts with RF User membership. This means that for any user assigned to the RF Analyst role, if the utility is open and saved, the user will also get the RF User role added to their security in addition to the RF Analyst role. This limits the user’s security to the lower level security.

Resolution: Corrected by adjusting the calculation method row logic to determine the RF role similar to the proposal accounting for the RF Analyst role. Previously, the logic checked for Admin membership and then defaulted to User.

Summary: In the RF Deductions Calculator, changes to the Discharges by Payor are unaffected at certain points when the user manually enters the discharges. For the entered discharges to be retained, the user would select Last Saved. However, this selection also changes the % Discharge by Payor section, resulting in issues with the discharge value.

Resolution: Corrected by updating the formula that links the % Discharges by Payor section drop-down choices to the drop-down choices in the Discharges by Payor section so that the two selection drop-downs are not connected.

Summary: In the RF Plan File, some Non-Operating revenue is appearing in the Operating Revenue section because RFCodes were not set up correctly.

Resolution: Corrected by updating the default data with the correct RFCodes.

IMPORTANT: Manual change – Please consider updating your RFCode.RFType and RFCode.RFSum to NonOpRev for the following codes:

• R_NonOp_Contrib
• R_NonOP_Extraprd
• R_NonOp_GainLoss
• R_NonOp_Interest
• R_NonOp_Invest
• R_NonOP_Other
• R_NonOp_Rev

Summary: The RF Stats Forecast calculation method is not calculating correctly because the absolute values in columns X, Y, and Z are being retained.

Resolution: Corrected by updating the formulas for the three calculation methods for the IP, OP, and Other Volume statistics in the RF Plan File template. Also corrected the IP Census Method in the Calc Method Library for negative rates.

Summary: In the RF Plan File, the Benefit as a % of Salaries calculation method is not accounting for the 1000 divisor, whether used as a fraction or a whole number, in column AB when summing periods.

Resolution: Corrected by updating the Pct of Salaries calculation method on the RF Plan File Forecast worksheet to include the scaling factor in the results of the Pct of Salaries row, column AB.

Summary: In the Deduction calculator, when a user tries to update the Rate/%ages in the payor sections, they receive a message that the cell is locked. A note on the calculator instructs the user to use the drop-down or revise the percentages as needed, but this is a function that was available in an older version of the calculator.

Resolution: Corrected by changing the cell formulas to allow user input for all forecast periods.

Summary: In the Forecast tab of the RF plan file, a defect in the formula for row 7, column W of the Salary block calculation method is causing issues with the section that identifies whether the labor is pulling in the fixed/variable percentages from the prior forecast or from the driver. Currently, this is defaulting to the prior forecast.

Resolution: Corrected the referencing problem in the cell formula, which was looking to the forecast method and not the RFCode.

Summary: The RF Consolidated Summary report for the current quarter is pulling data from the RF_Monthly table. However, if the user is forecasting mid quarter, the report is not pulling in the correct information. The RF_Monthly table contains the data prior to finalization through the plan files. When finalized, the data is written to the RF_Forecast table. This final data includes adjustments and additions made using the normal plan file process. The current report does not capture the latest information.

Resolution: Corrected by changing the reference from RF_Monthly to RF_Forecast for all fields imported from Axiom Query 3 to Axiom Query 6—the field definition for row 13 has been changed to reflect this.

Summary: In the Forecast tab of the RF plan file, when using the 1000s factor in the Benefits block, the forecasted columns for the current quarter contain an issue with the divide-by-1000s logic that is producing a rate that is under forecasted.

Resolution: Corrected by removing the /$W$31 from the formula in columns Q, R, and S, in the 12th row of the Pct of Salaries calculation method.

Summary: The RFHistory tab of the RF plan file is not summarizing statistics correctly and is not defaulting to calendar days, causing a difference in the per unit calculations on the RF History tab as compared to the RF Summary tab. In the RFHistory tab, a sum of the historical period statistics is used to see what specific statistics should be used by reviewing the results in cells R34:R38. There are no formulas in cells R34:R36 but there should be.

Resolution: Corrected by updating the formula logic in cells R34:R36.

Summary: In the RF Target Variance Report, a value of “6” added to the end of the RF Period formula in the Variables tab cell I15 is causing errors due to periods displaying greater than 12.

Resolution: Corrected by removing the added hard-coded “6” in the affected cell of the report.

Summary: In the RF Deduction calculator, a data filter added to Axiom Query 1 on the PayorCodes tab causes an issue when setting up the Administrative Adjustments, Charity, and BadDebt sections. The codes used are saving to the database but not being retrieved upon refresh because the filter is excluding those codes.

Resolution: Corrected by changing the data filter to include the Admin/Charity/BadDebt codes.

Summary: The Balance Sheet and Cash flow utility is using the system period and not the RFConfig period in cells J17 and K17. This is causing historical data to come in instead of forecasted data.

Resolution: Corrected by changing the reference for System Period and System Year to the RF Drivers equivalents.

Summary: The Forecast tab of the RF plan file may display a #NUM error if the Volume growth percentage exceeds negative 100%.

Resolution: Corrected by changing the logic for when the condition exists.

Summary: The Outpatient Section of the Deduction Calculator’s Summary tab is using the same row on the Deductions tab to get the values for the Admin/Charity/BadDebt for both Inpatient (IP) and Outpatient (OP). If, for example, the IP Charity is in row 1200, the OP Charity will be in row 1201. The formula does not reflect that both IP and OP are referencing row 1200.

Resolution: Corrected by changing the offset formulas so that they reference the correct row (one below the IP row) for the OP section.

Resolution: Corrected by adjusting the location of the frozen panes so the dates are always visible.

Summary: In the Deductions Calculator, when a user updates the Case Mix Index by Payor, the CMI Weights by Payor is summing in column AK instead of averaging the CMI weights.

Resolution: Corrected by replacing the formula so that it averages the CMI weights by payor instead of summing them. Also changed Weight code label to CMI Weight Code.

Summary: In the plan file Forecast tab, the Total row of the Other Deductions calculation method does not include adjustments for the forecast months, but it does include them for the actual months. The user is not able to enter a dollar amount when there is no revenue block.

Resolution: Corrected by updating the formula logic to include the ability to add a dollar amount regardless of whether there is an existing revenue block. The dollar amount added to adjustments displays in the total.

Summary: In the Deductions Calculator, there are two issues:

1. In the Deductions tab, for columns AI and AJ, the key statistic driver data (Patient Days, Discharges and Visits) is not coming into the calculator because cells I167 and I168 on the Variables tab are hard coded to RF_Monthly.Y0 when they should be variable and should reference either RF_Forecast or RF_Monthly.

   Resolution: Corrected by updating the formula logic on the Variables tab to reference the applicable data table.

2. The Deduction Calculator is not designed to roll, so the inputs do not move when they roll to the next quarter.

   Resolution: Corrected by adding the formula that was missing in the Variables tab so that the data rolls.

Summary: In the Deductions Calculator, there are a number of issues:

1. Manually entered values for the current months/quarters do not roll when the month/quarter changes. The code that retrieves the values is hard coded with zero.

   Resolution: Data was not rolling due to a missing GetData formula in cell X14 on the Variables tab that resolves to the RFPeriod stored in the RFDED table as “LastRFDED_YrQtr_Save” field. When this period is older than the Current Period, it evaluates when it is appropriate to roll data. Corrected by adding the missing GetData formula.

2. On the Deductions tab, columns AI and AJ are not pointing to the correct table to retrieve data when a user uses CYB or QFM for forecasted months.

   Resolution: Corrected by updating and adding formula logic to the Variables tab for rows 167 and 168 where the evaluation of the Waterfall or Forecast tables are determined.

3. The second future quarter to the first future quarter roll (QYB2 to QYB1) works. However, QYB1 also has three monthly components associated with month fields 1, 2, and 3. The quarterly amounts to roll appear in the quarterly column, but the monthly amounts are zero. As QYB2 rolls to QYB, the value should allocate to the month columns for any deemed forecast month.

   Resolution: Corrected by spreading the quarterly amount to roll from quarters to months. NOTE: This will impact the report only when the "Last Saved" option is selected.

4. In the Payor blocks, if columns AI and AJ are forecast columns in the current quarter, they do not update results if an input field changes.

   Resolution: Updated the formula in the payor blocks to accommodate changes.

Summary: The RF Consolidated Summary report is pulling in the adjustment amounts instead of the intended forecast amounts. This affects columns M through AJ.

Resolution: Corrected by updating the field definition in row 13 to resolve to the correct period.

Summary: In the plan file Forecast tab, if a user uses the Depreciation calculation method when the forecast method is Budget, the total line of the depreciation calculation method is not allowing the manual inputs to sum. Budget should be treated as a forecast method, allowing the summation to occur.

Resolution: Corrected by changing the summation logic to treat anything other than an actual period as a forecast period. The check for this is done in row 4, columns Q, R, and S.